package com.to8to.autotask.allocate.ecommercer;

import static com.to8to.autotask.util.AutoTaskType.ALLOCATE_DSGW;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Collections;
import java.util.Comparator;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.elasticsearch.common.collect.Maps;
import org.elasticsearch.common.collect.Sets;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.google.common.collect.Lists;
import com.to8to.autotask.resulthandler.FailHandler;
import com.to8to.autotask.rpc.SendMsg2ES;
import com.to8to.autotask.util.Constant;
import com.to8to.autotask.util.DBOperaType;
import com.to8to.autotask.util.DBUtil;
import com.to8to.autotask.util.TwoTuple;
import com.to8to.autotask.util.Utils;
import com.to8to.es.db.dao.DBTables;
import com.to8to.es.util.LogUtil;
import com.to8to.thrift.crm.itemdb.TItem;

/**
 * AllocateEcommercerAlgorithm.java
 * 作 者：atom.liu
 * 版 本：v2.0
 * 日 期：2014年11月21日
 * 描 述：自动分配电商顾问算法实现类。包访问权限。
 *     2015-01-21：分配电商顾问时，二次发标项目分配给之前项目的电商顾问id 
 */
final class AllocateEcommercerAlgorithm
{
    private static final Logger logger = LoggerFactory.getLogger(AllocateEcommercerAlgorithm.class);

    /**
     * SQL：自动分配时，要分配电商顾问的项目。
     * ispass = 1：通过审核(可售)
     * (dsgw_id = 0 or dsgw_id is null):还未分配电商顾问
     * cityid not in (?)：深圳、南京等城市的已售项目不分配。
     * cur_baoyue > 0：轮单到公司数>0
     * puttime >= ? & puttime < ?: 审核通过时间为昨天
     */
    private static final String AUTO_PROJECT_SQL =
        "SELECT ea.dsgw_id,e.source_yid,a.cityid,a.city,a.yid,a.cur_baoyue FROM to8to_yuyue_apply AS a "
            + " LEFT JOIN to8to_yuyue_apply_ext AS e ON a.yid = e.yid "
            + " LEFT JOIN to8to_yuyue_apply AS ea ON ea.yid = e.source_yid"
            + " WHERE a.ispass = 1 AND (a.dsgw_id = 0 OR a.dsgw_id IS NULL) AND a.cur_baoyue > 0 AND a.cityid not in (%s) AND a.puttime >= ? AND a.puttime < ?";

    /**
     * SQL：手动分配时，要分配电商顾问的项目。
     * ispass = 1 :通过可售
     * (dsgw_id = 0 or dsgw_id is null):还未分配电商顾问
     * cityid not in (?)：深圳、南京等城市的已售项目不分配。
     * cur_baoyue > 0：轮单到公司数>0
     * puttime >= 1396281600:2014-04-01之后通过审核的项目
     * service_status < 7:项目还未结束的
     */
    private static final String MANUAL_PROJECT_SQL =
        "SELECT ea.dsgw_id,e.source_yid,a.cityid,a.city,a.yid,a.cur_baoyue FROM to8to_yuyue_apply AS a"
            + " LEFT JOIN to8to_yuyue_apply_ext AS e ON a.yid = e.yid"
            + " LEFT JOIN to8to_yuyue_apply AS ea ON ea.yid = e.source_yid"
            + " WHERE a.ispass = 1 AND (a.dsgw_id = 0 OR a.dsgw_id IS NULL) AND a.cur_baoyue > 0 AND a.cityid not in (%s) AND a.puttime >= 1396281600 AND a.service_status < 7";

    /**
     * SQL:查询出符合条件的电商顾问。
     * crmroleid = 12:角色为电商顾问
     * cityids is not null and cityids != ''：有负责的城市
     * isclose = 0：在职
     * receive_status = 0：处于接待状态
     */
    private static final String ECOMMERCER_SQL =
        "select * from to8to_user_info where crmroleid = 12 and cityids is not null and cityids != '' and isclose = 0 and receive_status = 0";

    /**
     * SQL:分配电商顾问
     */
    private static final String ALLOCATE_SQL = "update to8to_yuyue_apply set dsgw_id= %s where yid = %s and dsgw_id = 0";

    /**
     * 获取需要更新dsgw_id的二次发标项目，用于通知es刷新
     */
    private static final String GET_SECOND_YID = "SELECT a.yid FROM to8to_yuyue_apply AS a "
        + " LEFT JOIN to8to_yuyue_apply_ext AS e ON e.yid = a.yid"
        + " LEFT JOIN to8to_yuyue_apply AS ea ON ea.yid = e.source_yid"
        + " WHERE ea.dsgw_id > 0 AND a.dsgw_id > 0 AND ea.dsgw_id != a.dsgw_id AND ea.cur_baoyue > 0 AND a.cur_baoyue > 0 AND a.cityid not in (%s)"
        + " AND a.puttime >= 1396281600 AND ea.puttime >= 1396281600 AND a.service_status < 7 AND ea.dsgw_id IN (%s)";

    /**
     * 更新二次发标的dsgw_id为原来的dsgw_id
     */
    private static final String UPDATE_SECOND_ZB = "UPDATE to8to_yuyue_apply AS a "
        + " LEFT JOIN to8to_yuyue_apply_ext AS e ON e.yid = a.yid"
        + " LEFT JOIN to8to_yuyue_apply AS ea ON ea.yid = e.source_yid"
        + " SET a.dsgw_id = ea.dsgw_id"
        + " WHERE ea.dsgw_id > 0 AND a.dsgw_id > 0 AND ea.dsgw_id != a.dsgw_id AND ea.cur_baoyue > 0 AND a.cur_baoyue > 0 AND a.cityid not in (%s) "
        + " AND a.puttime >= 1396281600 AND ea.puttime >= 1396281600 AND a.service_status < 7 AND ea.dsgw_id IN (%s)";

    /**
     * 排序策略
     */
    private static final StrategyWithAllocateCount STRATEGY = new StrategyWithAllocateCount();

    /**
     * 执行分配的主方法
     * @param isAutoTrigger 是否是自动触发。区别在于需要分配的项目不同。
     */
    public static void allocate(boolean isAutoTrigger)
    {
        Connection connect = null;
        try
        {
            connect = DBUtil.getConnection(false);
            Map<Integer, Ecommercer> ecerMap = getAssignableEcommercer(connect);
            if (Utils.isNullOrEmpty(ecerMap))
            {
                logger.info("当前可分配的电商顾问个数为 0");
                return;
            }

            Collection<Ecommercer> ecommercers = ecerMap.values();
            TwoTuple<List<TItem>, List<TItem>> tuple = getAssignableProject(connect, isAutoTrigger, ecerMap.keySet());
            if (Utils.isNullOrEmpty(tuple.first) && Utils.isNullOrEmpty(tuple.second))
            {
                logger.info("本次可分配项目个数为 0");
                return;
            }

            List<String> allocateSqlList = Lists.newArrayList();
            Set<String> updateYidSet = Sets.newHashSet();

            //二次发标的项目分配电商顾问
            for (TItem item : tuple.second)
            {
                Ecommercer ecer = ecerMap.get(item.getDsgw_id());
                allocateSqlList.add(String.format(ALLOCATE_SQL, ecer.getUid(), item.getYid()));
                updateYidSet.add(String.valueOf(item.getYid()));
                ecer.setAllocatedCount(ecer.getAllocatedCount() + 1);
                logger.info(String.format("二次发标项目[ %s ] 项目城市[ %s %s ] 分配到电商顾问  [ %s  %s]", item.getYid(), item.getCityid(), item.getCity(), ecer.getUid(), ecer.getUsername()));
            }

            //将项目集合和电商顾问集合根据城市id进行分组
            Map<Integer, List<Ecommercer>> cityEcomMap = getCityEcommercer(ecommercers);
            Map<Integer, List<TItem>> cityCountMap = getCityProject(tuple.first);

            //开始执行分配
            for (Map.Entry<Integer, List<TItem>> entry : cityCountMap.entrySet())
            {
                int cityid = entry.getKey();
                List<TItem> items = entry.getValue(); //该城市待分配的项目集合
                List<Ecommercer> ecers = cityEcomMap.get(cityid); //该城市电商顾问集合
                if (Utils.isNullOrEmpty(ecers))
                {
                    logger.info(String.format("该城市[%s %s]没有负责的电商顾问 ", cityid, items.get(0).getCity()));
                    continue;
                }
                for (int i = 0; i < items.size(); i++)
                {
                    //每次分配前，重新排序
                    Collections.sort(ecers, STRATEGY);

                    TItem item = items.get(i);
                    Ecommercer ecer = ecers.get(0);
                    allocateSqlList.add(String.format(ALLOCATE_SQL, ecer.getUid(), item.getYid()));
                    updateYidSet.add(String.valueOf(item.getYid()));
                    logger.info(String.format("审核通过项目[ %s ] 项目城市[ %s %s ] 分配到电商顾问  [ %s  %s]", item.getYid(), cityid, item.getCity(), ecer.getUid(), ecer.getUsername()));
                    ecer.setAllocatedCount(ecer.getAllocatedCount() + 1);
                }
            }

            Set<String> secondYids = getNeedUpdateYids(connect, ecerMap.keySet());
            if (!Utils.isNullOrEmpty(secondYids))
                updateYidSet.addAll(secondYids);

            DBUtil.refresh2DB(connect, allocateSqlList, ALLOCATE_DSGW, new SendMsg2ES(DBOperaType.UPDATE, DBTables.TABLE_APPLY, updateYidSet));
            //在分配完成之后，更新二次发标项目的dsgw_id，防止在分配时原项目和二次发标项目都没有dsgw_id，从而分给了不同的电商顾问
            updateSecondZB(connect, ecerMap.keySet(), secondYids);
        }
        catch (Throwable e)
        {
            logger.error("分配电商顾问异常！{} \n {}", e, LogUtil.logStackTrace(e));
            DBUtil.rollback(connect);
            if (e instanceof SQLException)
                FailHandler.handler(AllocateEcommercerTask.class);
        }
        finally
        {
            DBUtil.releaseSource(null, null, connect);
        }
    }

    /**
     * 增加需要更新dsgw_id的二次发标项目id，用于通知es刷新 
     */
    private static Set<String> getNeedUpdateYids(Connection connect, Set<Integer> ecerIds) throws Exception
    {
        Set<String> set = Sets.newHashSet();
        String sql = String.format(GET_SECOND_YID, Constant.getSpecCityStr(), Utils.collect2Str(ecerIds));
        PreparedStatement stat = connect.prepareStatement(sql);
        ResultSet result = stat.executeQuery();
        while (result.next())
            set.add(String.valueOf(result.getInt("a.yid")));
        DBUtil.releaseSource(result, stat, null);
        logger.info(String.format("本次需要更新dsgw_id的二次发标项目共 %s 个 \r\n %s", set.size(), set));
        return set;
    }

    /**
     * 更新二次中标项目，电商顾问id不一致的情况
     */
    private static void updateSecondZB(Connection connect, Set<Integer> ecerIds, Set<String> secondYids) throws SQLException
    {
        if (Utils.isNullOrEmpty(secondYids))
            return;
        String sql = String.format(UPDATE_SECOND_ZB, Constant.getSpecCityStr(), Utils.collect2Str(ecerIds));
        DBUtil.refresh2DB(connect, sql, ALLOCATE_DSGW, null);
    }

    /**
     * 获取符合要求的可分配的项目集合
     * @param connect 数据库连接
     * @param isAutoTrigger 是否是自动触发
     * @return twotuple.first 正常项目; twotuple.second 二次发标项目
     */
    private static TwoTuple<List<TItem>, List<TItem>> getAssignableProject(Connection connect, boolean isAutoTrigger, Collection<Integer> ecerIds)
            throws SQLException
    {
        List<TItem> firstList = Lists.newArrayList();
        List<TItem> secondList = Lists.newArrayList();//二次发表项目
        PreparedStatement stat = null;
        if (isAutoTrigger)
        {
            stat = connect.prepareStatement(String.format(AUTO_PROJECT_SQL, Constant.getSpecCityStr()));
            stat.setInt(1, Utils.getDayTimePoint(-1));
            stat.setInt(2, Utils.getDayTimePoint(0));
        }
        else
        {
            stat = connect.prepareStatement(String.format(MANUAL_PROJECT_SQL, Constant.getSpecCityStr()));
        }

        ResultSet result = stat.executeQuery();

        while (result.next())
        {
            TItem project = new TItem();
            int cityid = result.getInt("a.cityid");
            if (cityid == 0)
                continue;
            project.setYid(result.getInt("a.yid"));
            project.setCity(result.getString("a.city"));
            project.setCityid(cityid);
            project.setCur_baoyue(result.getInt("a.cur_baoyue"));

            int source_yid = result.getInt("e.source_yid");
            int second_dsgw_id = result.getInt("ea.dsgw_id");
            if (second_dsgw_id != 0 && source_yid != 0 && ecerIds.contains(second_dsgw_id))
            {
                project.setDsgw_id(second_dsgw_id);
                secondList.add(project);
            }
            else
            {
                firstList.add(project);
            }
        }

        DBUtil.releaseSource(result, stat, null);

        return TwoTuple.newInstance(firstList, secondList);
    }

    /**
     * 获取可分配的电商顾问 。
     */
    private static Map<Integer, Ecommercer> getAssignableEcommercer(Connection connect) throws SQLException
    {
        Map<Integer, Ecommercer> map = Maps.newHashMap();

        PreparedStatement stat = connect.prepareStatement(ECOMMERCER_SQL);
        ResultSet result = stat.executeQuery();
        while (result.next())
        {
            Ecommercer ecommercer = new Ecommercer();
            int uid = result.getInt("uid");
            ecommercer.setUid(uid);
            ecommercer.setUsername(result.getString("username"));
            ecommercer.setCityids(Utils.parseIntegerList(result.getString("cityids"), ","));
            map.put(uid, ecommercer);
        }

        DBUtil.releaseSource(result, stat, null);

        return map;
    }

    /**
     * 计算出每个城市可分配的项目的集合
     */
    private static Map<Integer, List<TItem>> getCityProject(List<TItem> projects)
    {
        Map<Integer, List<TItem>> map = Maps.newHashMap();
        for (TItem project : projects)
        {
            int cityid = project.getCityid();
            List<TItem> list = map.get(cityid);
            if (list == null)
            {
                list = new ArrayList<TItem>();
                map.put(cityid, list);
            }
            list.add(project);
        }
        return map;
    }

    /**
     * 将电商顾问按照城市进行分组，获得每个城市的电商顾问集合。
     */
    private static Map<Integer, List<Ecommercer>> getCityEcommercer(Collection<Ecommercer> allEcommer)
    {
        Map<Integer, List<Ecommercer>> map = Maps.newHashMap();

        for (Ecommercer ec : allEcommer)
        {
            for (Integer i : ec.getCityids())
            {
                List<Ecommercer> list = map.get(i);
                if (list == null)
                {
                    list = new ArrayList<Ecommercer>();
                    map.put(i, list);
                }
                list.add(ec);
            }
        }
        return map;
    }

    /**
     * 静态内部类。根据每个电商顾问的当天所分配的数量来进行排序。 
     */
    private static class StrategyWithAllocateCount implements Comparator<Ecommercer>
    {
        @Override
        public int compare(Ecommercer o1, Ecommercer o2)
        {
            int result = o1.getAllocatedCount() - o2.getAllocatedCount();
            if (result == 0)
                result = Utils.getRandomBoolean() ? -1 : 1;
            return result;
        }
    }
}
